package com.texas_android.app.utils.dateBase;

import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteException;
import android.database.sqlite.SQLiteOpenHelper;

import com.texas_android.app.http.bean.company.CompanyBean;
import com.texas_android.app.http.bean.company.ConfigLeaderBean;
import com.texas_android.app.http.bean.company.ProfessionalInfoBean;
import com.texas_android.app.http.bean.perssion.PerssionBean;
import com.texas_android.app.utils.Logger;

import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.List;

/**
 * Created by solin on 2018/7/3.
 */

public class DataBaseHelper extends SQLiteOpenHelper {
    public static String DB_PATH = "/data/data/com.texas_android.app/databases/";

    public static String DB_NAME = "myDBName.db";

    private SQLiteDatabase myDataBase;

    private final Context myContext;

    private static final int BUFFER_SIZE = 1024;

    /**
     * Constructor
     * Takes and keeps a reference of the passed context in order to access to the application assets and resources.
     *
     * @param context
     */
    public DataBaseHelper(Context context) {

        super(context, DB_NAME, null, 1);
        this.myContext = context;
    }

    /**
     * Creates a empty database on the system and rewrites it with your own database.
     */
    public void createDataBase() throws IOException {

        boolean dbExist = checkDataBase();

        if (dbExist) {
            //do nothing - database already exist
        } else {

            //By calling this method and empty database will be created into the default system path
            //of your application so we are gonna be able to overwrite that database with our database.
            this.getReadableDatabase();

            try {

                copyDataBase();

            } catch (IOException e) {

                throw new Error("Error copying database");

            }
        }

    }

    /**
     * Check if the database already exist to avoid re-copying the file each time you open the application.
     *
     * @return true if it exists, false if it doesn't
     */
    public boolean checkDataBase() {
        SQLiteDatabase checkDB = null;
        try {
            String myPath = DB_PATH + DB_NAME;
            checkDB = SQLiteDatabase.openDatabase(myPath, null, SQLiteDatabase.OPEN_READONLY);
        } catch (SQLiteException e) {
            //database does't exist yet.
            Logger.log("数据库不存在：");
        }
        if (checkDB != null) {
            checkDB.close();
        }
        return checkDB != null ? true : false;
    }

    /**
     * Copies your database from your local assets-folder to the just created empty database in the
     * system folder, from where it can be accessed and handled.
     * This is done by transfering bytestream.
     */
    private void copyDataBase() throws IOException {

        //Open your local db as the input stream
        InputStream myInput = myContext.getAssets().open(DB_NAME);

        // Path to the just created empty db
        String outFileName = DB_PATH + DB_NAME;

        //Open the empty db as the output stream
        OutputStream myOutput = new FileOutputStream(outFileName);

        //transfer bytes from the inputfile to the outputfile
        byte[] buffer = new byte[1024];
        int length;
        while ((length = myInput.read(buffer)) > 0) {
            myOutput.write(buffer, 0, length);
        }

        //Close the streams
        myOutput.flush();
        myOutput.close();
        myInput.close();

    }

    /**
     * 打开数据库
     *
     * @throws SQLException
     */
    public void openDataBase() throws SQLException {

        //Open the database
        String myPath = DB_PATH + DB_NAME;
        myDataBase = SQLiteDatabase.openDatabase(myPath, null, SQLiteDatabase.OPEN_READONLY);

    }


    /**
     * 关闭数据库
     */
    @Override
    public synchronized void close() {

        if (myDataBase != null)
            myDataBase.close();

        super.close();

    }

    @Override
    public void onCreate(SQLiteDatabase db) {

    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {

    }


//    public void importDatabase() {
//        // 存放数据库的目录
//        String dirPath = "/data/data/com.example.contact/databases";
//        File dir = new File(dirPath);
//        if (!dir.exists()) {
//            dir.mkdir();
//        }
//        // 数据库文件
//        File file = new File(dir, "contact.db");
//        try {
//            if (!file.exists()) {
//                file.createNewFile();
//            }
//            // 加载需要导入的数据库
//            InputStream is = myContext.getApplicationContext().getResources()
//                    .openRawResource(R.raw.contact);
//            FileOutputStream fos = new FileOutputStream(file);
//            byte[] buffere = new byte[is.available()];
//            is.read(buffere);
//            fos.write(buffere);
//            is.close();
//            fos.close();
//
//        } catch (FileNotFoundException e) {
//            e.printStackTrace();
//        } catch (IOException e) {
//            e.printStackTrace();
//        }
//    }


    @SuppressWarnings("ResultOfMethodCallIgnored")
    public void copyDBFile() {
        File dir = new File(DB_PATH);
        if (!dir.exists()) {
            dir.mkdirs();
        }
        File dbFile = new File(DB_PATH + DB_NAME);
        if (!dbFile.exists()) {
            InputStream is;
            OutputStream os;
            try {
                is = myContext.getResources().getAssets().open(DB_NAME);
                os = new FileOutputStream(dbFile);
                byte[] buffer = new byte[BUFFER_SIZE];
                int length;
                while ((length = is.read(buffer, 0, buffer.length)) > 0) {
                    os.write(buffer, 0, length);
                }
                os.flush();
                os.close();
                is.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
    }

    /**
     * 查询 人员
     *
     * @param keyword
     * @return
     */
    public List<PerssionBean> searchPersion(final String keyword) {
        // or pinyin like "%" + keyword + "%"
        SQLiteDatabase db = SQLiteDatabase.openOrCreateDatabase(DB_PATH + DB_NAME, null);
        Cursor cursor = db.rawQuery("select * from " + "h_user" + " where name like \"%" + keyword
                + "%\"", null);
        List<PerssionBean> result = new ArrayList<>();
        PerssionBean bean = null;
        while (cursor.moveToNext()) {
            bean = new PerssionBean();
            bean.setBirthdaty(cursor.getString(cursor.getColumnIndex("birthday")));
            bean.setAge(cursor.getInt(cursor.getColumnIndex("id")));
            bean.setName(cursor.getString(cursor.getColumnIndex("name")));
            bean.setId(cursor.getInt(cursor.getColumnIndex("id")));
            int sex = cursor.getInt(cursor.getColumnIndex("sex"));
            bean.setNation(cursor.getString(cursor.getColumnIndex("mz")));
            bean.setNativePlace(cursor.getString(cursor.getColumnIndex("jg")));
            bean.setBirthplace(cursor.getString(cursor.getColumnIndex("csd")));
            bean.setPartyDate(cursor.getString(cursor.getColumnIndex("rdsj")));
            bean.setWorkDate(cursor.getString(cursor.getColumnIndex("cjgzsj")));
            bean.setHealthStatus(cursor.getString(cursor.getColumnIndex("jkzk")));
            bean.setProfessionalPoint(cursor.getString(cursor.getColumnIndex("sxzyyhzc")));
            bean.setHeadIconurl(cursor.getString(cursor.getColumnIndex("filePath")));
//            bean.setNativePlace();
            if (sex == 1) {
                bean.setSex("男");
            } else if (sex == 2) {
                bean.setSex("女");
            } else {
                bean.setSex("未知");
            }

            result.add(bean);
            Logger.log("--------数据-------");
            Logger.log("查询数据:" + bean.toString());

        }
        cursor.close();
        db.close();
        return result;
    }

    /**
     * 个人职务
     *
     * @param userId
     * @param bean
     */
    public void searchZyjs(int userId, PerssionBean bean) {
        SQLiteDatabase db = SQLiteDatabase.openOrCreateDatabase(DB_PATH + DB_NAME, null);
        Cursor cursor = db.rawQuery("select * from " + "bg_zyjszwa019" + " where userId like \"%" + userId
                + "%\"", null);

        while (cursor.moveToNext()) {
            bean.setProfessionalDuty(cursor.getString(cursor.getColumnIndex("zyjszwzghz")));
            Logger.log("职务:" + bean.getProfessionalDuty());
        }
        cursor.close();
        db.close();
    }

    /**
     * 简历
     *
     * @param userId
     * @param bean
     */
    public void searchJianli(int userId, PerssionBean bean) {
        SQLiteDatabase db = SQLiteDatabase.openOrCreateDatabase(DB_PATH + DB_NAME, null);
        Logger.log("uiserId:" + userId);
        Cursor cursor = db.rawQuery("select * from " + "bg_grjla029" + " where userId =" + userId, null);
        String offer = "";
        while (cursor.moveToNext()) {
            offer = offer + cursor.getString(cursor.getColumnIndex("qssj")) + "--" + cursor.getString(cursor.getColumnIndex("zzsj"))
                    + cursor.getString(cursor.getColumnIndex("szdwjzw")) + "\n";
        }
        Logger.log("简历:" + offer);
        bean.setOffers(offer);
        cursor.close();
        db.close();
    }

    /**
     * 简历
     *
     * @param userId
     * @param bean
     */
    public void searchJiangcheng(int userId, PerssionBean bean) {
        SQLiteDatabase db = SQLiteDatabase.openOrCreateDatabase(DB_PATH + DB_NAME, null);
        Logger.log("uiserId:" + userId);
        Cursor cursor = db.rawQuery("select * from " + "bg_jcqka039" + " where userId =" + userId, null);
        String offer = "";
        while (cursor.moveToNext()) {
            offer = offer + cursor.getString(cursor.getColumnIndex("jcpzsj")) +
                    cursor.getString(cursor.getColumnIndex("sjcszwcc")) + "时--" +
                    cursor.getString(cursor.getColumnIndex("jcmcdm")) + "\n";
            Logger.log("奖惩情况:" + cursor.getString(cursor.getColumnIndex("jcpzsj")) + cursor.getString(cursor.getColumnIndex("jcmcdm")));

        }
        bean.setRewardPunishments(offer);
        cursor.close();
        db.close();
    }

    /**
     * 年度考核
     *
     * @param userId
     * @param bean
     */
    public void searchYearCheck(int userId, PerssionBean bean) {
        SQLiteDatabase db = SQLiteDatabase.openOrCreateDatabase(DB_PATH + DB_NAME, null);
        Cursor cursor = db.rawQuery("select * from " + "bg_ndkha035" + " where userId like \"%" + userId
                + "%\"", null);
        String yearcheck = "";
        while (cursor.moveToNext()) {
            yearcheck = yearcheck + cursor.getString(cursor.getColumnIndex("khnd")) + "    -   " +
                    cursor.getString(cursor.getColumnIndex("khjg")) + "\n";
            bean.setYearCheck(yearcheck);
            Logger.log("年度考核:" + bean.getProfessionalDuty());
        }
        cursor.close();
        db.close();
    }

    /**
     * 现任职务
     *
     * @param userId
     * @param bean
     */
    public void searchNowDuty(int userId, PerssionBean bean) {
        SQLiteDatabase db = SQLiteDatabase.openOrCreateDatabase(DB_PATH + DB_NAME, null);
        Cursor cursor = db.rawQuery("select * from " + "bg_zwxxa009" + " where userId like \"%" + userId
                + "%\"", null);

        while (cursor.moveToNext()) {
            bean.setNowDuty(cursor.getString(cursor.getColumnIndex("rzjg")));
            Logger.log("现任职务:" + bean.getProfessionalDuty());
        }
        cursor.close();
        db.close();
    }

    /**
     * 学历
     *
     * @param userId
     * @param bean
     */
    public void searchXueli(int userId, PerssionBean bean) {
        SQLiteDatabase db = SQLiteDatabase.openOrCreateDatabase(DB_PATH + DB_NAME, null);
        Cursor cursor = db.rawQuery("SELECT id,jylb,xl,xxjyx,rxsj FROM bg_xljxwa007 WHERE userid=" + userId, null);

        while (cursor.moveToNext()) {
            String status = cursor.getString(cursor.getColumnIndex("jylb"));
            if (!status.equals("全日制")) {
                bean.setWorkEduSchool(cursor.getString(cursor.getColumnIndex("xxjyx")));
                bean.setWorkEducatiodn(cursor.getString(cursor.getColumnIndex("xl")));
            } else {
                bean.setStandardSchool(cursor.getString(cursor.getColumnIndex("xxjyx")));
                bean.setStandardEducation(cursor.getString(cursor.getColumnIndex("xl")));
            }
//            bean.setYearCheck();
            Logger.log("教育:" + bean.toString());
        }
        cursor.close();
        db.close();
    }

    /**
     * 查询 单位
     *
     * @param keyword
     * @return
     */
    public List<CompanyBean> searchCompany(final String keyword) {
        SQLiteDatabase db = SQLiteDatabase.openOrCreateDatabase(DB_PATH + DB_NAME, null);
        List<CompanyBean> result = new ArrayList<>();
        Cursor cursor = null;
        try {
            cursor = db.rawQuery("select * from " + "h_dept" + " where simplename like \"%" + keyword
                    + "%\" or fullname like \"%" + keyword
                    + "%\"", null);
            CompanyBean bean;
            while (cursor.moveToNext()) {
                bean = new CompanyBean();
                bean.setCompanyName(cursor.getString(cursor.getColumnIndex("fullname")));
                bean.setCompanyShortName(cursor.getString(cursor.getColumnIndex("simplename")));
                ConfigLeaderBean configLeaderBean = new ConfigLeaderBean();
                configLeaderBean.setZzld(cursor.getInt(cursor.getColumnIndex("zzld")));
                configLeaderBean.setFzld(cursor.getInt(cursor.getColumnIndex("fzld")));
                configLeaderBean.setZzfld(cursor.getInt(cursor.getColumnIndex("zzfld")));
                configLeaderBean.setFzfld(cursor.getInt(cursor.getColumnIndex("fzfld")));
                bean.setConfigLeaderList(configLeaderBean);
                //行政类别
                bean.setCompanyType(cursor.getInt(cursor.getColumnIndex("dwxzlb")) + "");
                //籍贯级别
                bean.setCompanyRank(cursor.getInt(cursor.getColumnIndex("jgjb")) + "");
                bean.setCompanyId(cursor.getInt(cursor.getColumnIndex("id")));
                bean.setHrPerNum(cursor.getInt(cursor.getColumnIndex("bzslxzbz")));
                bean.setPublicPerNum(cursor.getInt(cursor.getColumnIndex("bzslsycgbz")));
                bean.setPrivatePerNum(cursor.getInt(cursor.getColumnIndex("bzslsyfcgbz")));
                result.add(bean);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (cursor != null)
                cursor.close();
            if (db != null)
                db.close();
        }
        return result;
    }

    /**
     * 行政实际人数
     *
     * @param bean
     */
    public void searchNubHr(int compId, CompanyBean bean) {
        SQLiteDatabase db = SQLiteDatabase.openOrCreateDatabase(DB_PATH + DB_NAME, null);
        //and rylb in('1','2') and status=1
        Cursor cursor = db.rawQuery("select * from  h_user " + " where deptid like \"%" + compId
                + "%\"  AND status=1  and (rylb like '1' or rylb like '2') ", null);

        while (cursor.moveToNext()) {
            int x = 1;
            Logger.log("行政实际人数:" + x);
            x++;
            bean.setHrPracticalNum(x);
        }
        cursor.close();
        db.close();
    }

    /**
     * 事业编参公实际人数
     *
     * @param bean
     */
    public void searchSybCg(int compId, CompanyBean bean) {
        SQLiteDatabase db = SQLiteDatabase.openOrCreateDatabase(DB_PATH + DB_NAME, null);
        //and rylb in('1','2') and status=1
        Cursor cursor = db.rawQuery("select * from  h_user " + " where deptid like \"%" + compId
                + "%\"  AND status=1  and rylb like '3' ", null);

        while (cursor.moveToNext()) {
            int x = 1;
            Logger.log("事业编参公实际人数:" + x);
            x++;
            bean.setPublicPerPracticalNum(x);
        }
        cursor.close();
        db.close();
    }

    /**
     * 事业编非参公实际人数
     *
     * @param bean
     */
    public void searchShiyebianfgong(int compId, CompanyBean bean) {
        SQLiteDatabase db = SQLiteDatabase.openOrCreateDatabase(DB_PATH + DB_NAME, null);
        //and rylb in('1','2') and status=1
        Cursor cursor = db.rawQuery("select * from  h_user " + " where deptid like \"%" + compId
                + "%\"  AND status=1  and rylb like '4' ", null);

        while (cursor.moveToNext()) {
            int x = 1;
            Logger.log("事业编非参公实际人数:" + x);
            x++;
            bean.setPrivatePerPracticalNum(x);
        }
        cursor.close();
        db.close();
    }

    /**
     * 查询实际配置人数 正职领导  副职领导 正职非领导  副职非领导
     */
    public void searchZZLeader(int deptId, CompanyBean bean) {
        SQLiteDatabase db = SQLiteDatabase.openOrCreateDatabase(DB_PATH + DB_NAME, null);
        //and rylb in('1','2') and status=1
        Cursor cursor = db.rawQuery("select * from  h_user " + " where deptid like \"%" + deptId
                + "%\"   and gwdm like '1' ", null);
        while (cursor.moveToNext()) {
            int x = 1;
            Logger.log("领导人数:" + x);
            x++;
            bean.getPracticalLeaderList().setSpzzld(x);
        }
        cursor.close();
        db.close();
    }

    /**
     * 副职领导
     *
     * @param deptId
     * @param bean
     */
    public void searchFZLeader(int deptId, CompanyBean bean) {
        SQLiteDatabase db = SQLiteDatabase.openOrCreateDatabase(DB_PATH + DB_NAME, null);
        //and rylb in('1','2') and status=1
        Cursor cursor = db.rawQuery("select * from  h_user " + " where deptid like \"%" + deptId
                + "%\"   and gwdm like '2' ", null);

        while (cursor.moveToNext()) {
            int x = 1;
            Logger.log("副职领导人数:" + x);
            x++;
            bean.getPracticalLeaderList().setSpfzld(x);
        }
        cursor.close();
        db.close();
    }

    /**
     * 正职非领导
     *
     * @param deptId
     * @param bean
     */
    public void searchZZFLeader(int deptId, CompanyBean bean) {
        SQLiteDatabase db = SQLiteDatabase.openOrCreateDatabase(DB_PATH + DB_NAME, null);
        //and rylb in('1','2') and status=1
        Cursor cursor = db.rawQuery("select * from  h_user " + " where deptid = \"%" + deptId
                + "%\"   and gwdm like '3' ", null);

        while (cursor.moveToNext()) {
            Logger.log("正职非领导:" + cursor.getCount());
            bean.getPracticalLeaderList().setSpzzfld(cursor.getCount());
        }
        cursor.close();
        db.close();
    }

    /**
     * 副职非领导
     *
     * @param deptId
     * @param bean
     */
    public void searchFZFLeader(int deptId, CompanyBean bean) {
        SQLiteDatabase db = SQLiteDatabase.openOrCreateDatabase(DB_PATH + DB_NAME, null);
        //and rylb in('1','2') and status=1
        Cursor cursor = db.rawQuery("select * from  h_user " + " where deptid like \"%" + deptId
                + "%\"   and gwdm like '4' ", null);

        while (cursor.moveToNext()) {
            bean.getPracticalLeaderList().setSpfzfld(cursor.getCount());
        }
        cursor.close();
        db.close();
    }

    /**
     * 任职要素查询
     *
     * @param deptId
     * @param bean
     */
    public void searchRenzhiyaoshu(int deptId, CompanyBean bean) {
        SQLiteDatabase db = SQLiteDatabase.openOrCreateDatabase(DB_PATH + DB_NAME, null);
        //and rylb in('1','2') and status=1
        Logger.log("deptId=" + deptId);
        Cursor cursor = db.rawQuery("SELECT t1.id eduid,t.gwid,t.gwxh,GROUP_CONCAT(t1.name) name FROM gw_ys t\n" +
                "LEFT JOIN education t1 ON t.yaosuid = t1.id\n" +
                " WHERE t.deptid =" + deptId +
                " GROUP BY t.gwid,t.gwxh", null);
        ProfessionalInfoBean infoBean = new ProfessionalInfoBean();
        while (cursor.moveToNext()) {
            if (cursor.getInt(cursor.getColumnIndex("gwid")) == 1) {
                //正职领导
                infoBean.setYszzld(cursor.getString(cursor.getColumnIndex("name")));
            } else if (cursor.getInt(cursor.getColumnIndex("gwid")) == 2) {
                //副职领导
                infoBean.setYsfzld(cursor.getString(cursor.getColumnIndex("name")));
            } else if (cursor.getInt(cursor.getColumnIndex("gwid")) == 3) {
                infoBean.setYszzfld(cursor.getString(cursor.getColumnIndex("name")));
                //正职非领导
            } else {
                //副职非领导
                infoBean.setYsfzfld(cursor.getString(cursor.getColumnIndex("name")));
            }
            Logger.log("任职要素查询:" + cursor.getString(cursor.getColumnIndex("name")));
            bean.setProfessionalInfoList(infoBean);
        }
        cursor.close();
        db.close();
    }
}
